package Yangtz.Cs.DAO;

import Yangtz.Cs.Utils.JDBCUtil;
import Yangtz.Cs.Entity.ReaderBean;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class ReaderDao {  //用来对用户表进行增删改查
    //添加用户
    public boolean insert(ReaderBean reader) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String sql = null;
        try {
            // 获得数据的连接
            conn = new JDBCUtil().GetConnection();
            // 获得Statement对象
            stmt = conn.createStatement();
            // 发送SQL语句
            sql = "INSERT INTO TB_Reader " +
                    "VALUES('"
                    + reader.getRdID()
                    + "','"
                    + reader.getRdName()
                    + "','"
                    + reader.getRdSex()
                    + "','"
                    + reader.getRdType()
                    + "','"
                    + reader.getRdDept()
                    + "','"
                    + reader.getRdPhone()
                    + "','"
                    + reader.getRdEmail()
                    + "','"
                    + reader.getRdBorrowQty()
                    + "','"
                    + reader.getRdPWd()
                    + "','"
                    + reader.getRdAdminRoles()
                    + "')";
            int num = stmt.executeUpdate(sql);
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(rs, stmt, conn);
        }
        return false;
    }

    // 查询所有的User对象
    public ArrayList<ReaderBean> findAll() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<ReaderBean> list = new ArrayList<ReaderBean>();
        try {
            // 获得数据的连接
            conn = new JDBCUtil().GetConnection();
            // 获得Statement对象
            stmt = conn.createStatement();
            // 发送SQL语句
            String sql = "SELECT * FROM TB_Reader";
            rs = stmt.executeQuery(sql);
            // 处理结果集
            while (rs.next()) {
                ReaderBean reader = new ReaderBean();
                reader.setRdID(rs.getString("rdID"));
                reader.setRdName(rs.getString("rdName"));
                reader.setRdSex(rs.getString("rdSex"));
                reader.setRdType(rs.getInt("rdType"));
                reader.setRdDept(rs.getString("rdDept"));
                reader.setRdPhone(rs.getString("rdPhone"));
                reader.setRdEmail(rs.getString("rdEmail"));
                reader.setRdBorrowQty(rs.getInt("rdBorrowQty"));
                reader.setRdPWd(rs.getString("rdPWd"));
                reader.setRdAdminRoles(rs.getString("rdAdminRoles"));
                list.add(reader);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(rs, stmt, conn);
        }
        return null;
    }

    // 根据id查找指定的user
    public ReaderBean find(String id) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            conn = new JDBCUtil().GetConnection();
            // 获得Statement对象
            stmt = conn.createStatement();
            // 发送SQL语句
            String sql = "SELECT * FROM TB_Reader WHERE rdID=" + id;
            rs = stmt.executeQuery(sql);
            // 处理结果集
            while (rs.next()) {
                ReaderBean reader = new ReaderBean();
                reader.setRdID(rs.getString("rdID"));
                reader.setRdName(rs.getString("rdName"));
                reader.setRdSex(rs.getString("rdSex"));
                reader.setRdType(rs.getInt("rdType"));
                reader.setRdDept(rs.getString("rdDept"));
                reader.setRdPhone(rs.getString("rdPhone"));
                reader.setRdEmail(rs.getString("rdEmail"));
                reader.setRdBorrowQty(rs.getInt("rdBorrowQty"));
                reader.setRdPWd(rs.getString("rdPWd"));
                reader.setRdAdminRoles(rs.getString("rdAdminRoles"));
                return reader;
            }
            return null;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(rs, stmt, conn);
        }
        return null;
    }

    // 删除用户
    public boolean delete(String id) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            conn = new JDBCUtil().GetConnection();
            // 获得Statement对象
            stmt = conn.createStatement();
            // 发送SQL语句
            String sql = "DELETE FROM TB_Reader WHERE rdID=" + id;
            int num = stmt.executeUpdate(sql);
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(rs, stmt, conn);
        }
        return false;
    }

    // 修改用户
    public boolean update(ReaderBean reader) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 获得数据的连接
            conn = new JDBCUtil().GetConnection();
            // 获得Statement对象
            stmt = conn.createStatement();
            // 发送SQL语句

            String sql = "UPDATE TB_Reader set " +
                    "rdName='" + reader.getRdName() +
                    "',rdSex='" + reader.getRdSex() +
                    "',rdType='" + reader.getRdType() +
                    "',rdDept='" + reader.getRdDept() +
                    "',rdPhone='" + reader.getRdType() +
                    "',rdEmail='" + reader.getRdEmail() +
                    "',rdBorrowQty='" + reader.getRdBorrowQty() +
                    "',rdPwd='" + reader.getRdPWd() +
                    "',rdAdminRoles='" + reader.getRdAdminRoles() +
                    "' WHERE rdID=" + reader.getRdID();
            int num = stmt.executeUpdate(sql);
            if (num > 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(rs, stmt, conn);
        }
        return false;
    }
}
